package com.sp2p.dao;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang.StringEscapeUtils;
import org.apache.commons.lang.StringUtils;

import com.shove.data.DataException;
import com.shove.data.DataSet;
import com.shove.data.dao.MySQL;
import com.shove.util.BeanMapUtils;
import com.shove.vo.PageBean;
import com.sp2p.database.Dao;

public class GameDao {

	/**
	 * @MethodName: querylotteryGift
	 * @Param: GameDao
	 * @Author: wujc
	 * @Date: 2014-12-22 上午11:10:04
	 * @Return:
	 * @Descb: 查询推盘活动奖品信息
	 * @Throws:
	 */
	public List<Map<String, Object>> queryLotteryGift(Connection conn,
			long activtyId) throws SQLException, DataException {
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT lotteryIndex,weight,remarks FROM t_game_lottery_gift a where a.activityId = "
						+ activtyId);
		DataSet dataSet = MySQL.executeQuery(conn, sql.toString());
		dataSet.tables.get(0).rows.genRowsMap();
		sql = null;
		return dataSet.tables.get(0).rows.rowsMap;
	}
	
	public List<Map<String, Object>> queryLotteryAward(Connection conn,
			long activtyId) throws SQLException, DataException {
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT CONCAT(SUBSTRING(username,1,1),'***') as username,c.remarks FROM t_game_lottery_award a ");
		sql.append(" LEFT JOIN t_user b on a.userId = b.id ");
		sql.append(" LEFT JOIN t_game_lottery_gift c on c.lotteryIndex = a.giftIndex and c.activityId = a.activityId ");
		sql.append(" WHERE a.activityId = " + activtyId);
		sql.append(" ORDER BY a.id DESC ");
		DataSet dataSet = MySQL.executeQuery(conn, sql.toString());
		dataSet.tables.get(0).rows.genRowsMap();
		sql = null;
		return dataSet.tables.get(0).rows.rowsMap;
	}
	
	public Map<String, String> queryLotteryAwardById(Connection conn,String unionid,
			long awardId, long activityId) throws Exception {
		StringBuffer command = new StringBuffer();
		command.append(" select tga.*,tgf.giftType,tgf.amount ");
		command.append(" from  t_game_lottery_award tga ");
		command.append(" left join t_game_lottery_gift tgf ");
		command.append(" on tga.giftIndex = tgf.lotteryIndex and tga.activityId = tgf.activityId ");
		command.append(" where tga.unionid = '" + unionid + "'");
		command.append(" and tga.activityId = " + activityId);
		command.append(" and tga.id = " + awardId);
		DataSet dataSet = MySQL.executeQuery(conn, command.toString());
		return BeanMapUtils.dataSetToMap(dataSet);
	}

	public Map<String, String> queryLotteryUserInfo(Connection conn,
			long userid, long activityId) throws Exception {
		StringBuffer command = new StringBuffer();
		command.append(" select userId,chanceNum,awardNum ");
		command.append(" from  t_game_lottery_user ");
		command.append(" where userId = " + userid);
		command.append(" and activityId = " + activityId);
		DataSet dataSet = MySQL.executeQuery(conn, command.toString());
		return BeanMapUtils.dataSetToMap(dataSet);
	}
	
	public Map<String, String> queryLotteryUserInfo(Connection conn,
			String unionid, long activityId) throws Exception {
		StringBuffer command = new StringBuffer();
		command.append(" select userId,chanceNum,awardNum,hasPlayNum ");
		command.append(" from  t_game_lottery_user ");
		command.append(" where unionid = '" + unionid + "'");
		command.append(" and activityId = " + activityId);
		DataSet dataSet = MySQL.executeQuery(conn, command.toString());
		return BeanMapUtils.dataSetToMap(dataSet);
	}
	
	public Map<String, String> queryLotteryHasPlayTimes(Connection conn,
			long userid, long activityId) throws Exception {
		StringBuffer command = new StringBuffer();
		command.append(" select count(id) as playTimes ");
		command.append(" from  t_game_lottery_award ");
		command.append(" where userId = " + userid);
		command.append(" and activityId = " + activityId);
		command.append(" and to_days(crtime) = to_days(CURDATE()) ");
		DataSet dataSet = MySQL.executeQuery(conn, command.toString());
		return BeanMapUtils.dataSetToMap(dataSet);
	}
	
	/**
	 * @MethodName: updateLotteryUserChanceNum
	 * @Param: GameDao
	 * @Author: wujc
	 * @Date: 2015-06-10
	 * @Return:
	 * @Descb: 增加游戏用户参与次数
	 * @Throws:
	 */
	public long updateLotteryUserChanceNum(Connection conn, String unionid,
			long activityId) throws SQLException {
		long returnId = -1;
		StringBuffer command = new StringBuffer();
		command.append(" UPDATE t_game_lottery_user SET chanceNum = chanceNum + 1");
		command.append(" WHERE unionid = '" + unionid + "'");
		command.append(" and activityId = " + activityId);
		returnId = MySQL.executeNonQuery(conn, command.toString());
		return returnId;
	}
	

	/**
	 * @MethodName: updateLotteryUserChance
	 * @Param: GameDao
	 * @Author: wujc
	 * @Date: 2014-12-22 下午04:47:06
	 * @Return:
	 * @Descb: 更新游戏用户参与次数，有效获奖次数
	 * @Throws:
	 */
	public long updateLotteryUserHasPlayNum(Connection conn, String unionid,
			long activityId, int isAward) throws SQLException {
		long returnId = -1;
		StringBuffer command = new StringBuffer();
		command.append(" UPDATE t_game_lottery_user SET chanceNum = chanceNum - 1,hasPlayNum = hasPlayNum + 1, ");
		command.append(" awardNum = awardNum + " + isAward);
		command.append(" WHERE unionid = '" + unionid + "'");
		command.append(" and activityId = " + activityId);
		returnId = MySQL.executeNonQuery(conn, command.toString());
		return returnId;
	}
	/**
	 * @MethodName: insertLotteryUser
	 * @Param: GameDao
	 * @Author: wujc
	 * @Date: 2015-06-10
	 * @Return:
	 * @Descb: 增加用户信息
	 * @Throws:
	 */
	public long insertLotteryUser(Connection conn, String unionid,
			long activityId, int chanceNum) throws SQLException {
		long returnId = -1;
		StringBuffer command = new StringBuffer();
		command.append("  INSERT INTO t_game_lottery_user (activityId, unionid, chanceNum) ");
		command.append(" VALUES (" + activityId);
		command.append(" ,'" + unionid + "'");
		command.append(" ," + chanceNum);
		command.append(" ) ");
		returnId = MySQL.executeNonQuery(conn, command.toString());
		return returnId;
	}

	/**
	 * @MethodName: insertLotteryAward
	 * @Param: GameDao
	 * @Author: wujc
	 * @Date: 2014-12-22 下午04:47:06
	 * @Return:
	 * @Descb: 增加用户获奖信息
	 * @Throws:
	 */
	public long insertLotteryAward(Connection conn, String unionid,
			long activityId, int giftIndex) throws SQLException {
		long returnId = -1;
		StringBuffer command = new StringBuffer();
		command
				.append(" INSERT t_game_lottery_award(activityId,unionid,giftIndex,crtime) ");
		command.append(" VALUES (" + activityId);
		command.append(" ,'" + unionid + "'");
		command.append(" ," + giftIndex);
		command.append(" ,NOW()) ");
		returnId = MySQL.executeNonQuery(conn, command.toString());
		return returnId;
	}
	
	/**
	 * @MethodName: updateLotteryAward
	 * @Param: GameDao
	 * @Author: wujc
	 * @Date: 2015-06-11
	 * @Return:
	 * @Descb: 更新用户兑换品信息
	 * @Throws:
	 */
	public long updateLotteryAward(Connection conn,
			long awardId, long userId) throws SQLException {
		long returnId = -1;
		StringBuffer command = new StringBuffer();
		command.append(" UPDATE t_game_lottery_award SET userId = " + userId +", ");
		command.append(" isPay = 1 ");
		command.append(" WHERE id = " + awardId);
		returnId = MySQL.executeNonQuery(conn, command.toString());
		return returnId;
	}
	
	/**
	 * @MethodName: querySheepMWAward
	 * @Param: GameDao
	 * @Author: wujc
	 * @Date: 2014-12-22 上午11:10:04
	 * @Return:
	 * @Descb: 查询炮打大灰狼游戏奖励信息
	 * @Throws:
	 */
	public Map<String, String>  querySheepMWAward(Connection conn,
			String mobilePhone) throws SQLException, DataException {
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT mobilePhone,awardType,awardAmount,awardStatus,crtime ");
		sql.append(" FROM t_game_sheepmw_award ");
		sql.append(" WHERE mobilePhone = '" + mobilePhone +"' ");
		DataSet dataSet = MySQL.executeQuery(conn, sql.toString());
		return BeanMapUtils.dataSetToMap(dataSet);
	}
	
	/**
	 * @MethodName: insertSheepMWAward
	 * @Param: GameDao
	 * @Author: wujc
	 * @Date: 
	 * @Return:
	 * @Descb: 增加炮打大灰狼游戏奖励信息
	 * @Throws:
	 */
	public long insertSheepMWAward(Connection conn, String mobilePhone,
			int awardType,Double awardAmount, int awardStatus) throws SQLException {
		long returnId = -1;
		StringBuffer command = new StringBuffer();
		command.append(" INSERT t_game_sheepmw_award(mobilePhone,awardType,awardAmount,awardStatus,crtime) ");
		command.append(" VALUES (" + mobilePhone);
		command.append(" ," + awardType);
		command.append(" ," + awardAmount);
		command.append(" ," + awardStatus);
		command.append(" ,NOW()) ");
		returnId = MySQL.executeNonQuery(conn, command.toString());
		return returnId;
	}
	
	/**
	 * @MethodName: updateSheepMWAwardStatus
	 * @Param: GameDao
	 * @Author: wujc
	 * @Date: 
	 * @Return:
	 * @Descb: 更新炮打大灰狼游戏奖励状态
	 * @Throws:
	 */
	public long updateSheepMWAwardStatus(Connection conn, String mobilePhone,
			int awardStatus) throws SQLException {
		long returnId = -1;
		StringBuffer command = new StringBuffer();
		command.append(" UPDATE t_game_sheepmw_award SET awardStatus =  " + awardStatus);
		command.append(" WHERE mobilePhone = " + mobilePhone);
		returnId = MySQL.executeNonQuery(conn, command.toString());
		return returnId;
	}
	
	
	/**
	 * @MethodName: querySpliteEnvelopeByUserId
	 * @Param: GameDao
	 * @Author: wujc
	 * @Date: 
	 * @Return:
	 * @Descb: 查询分裂红包信息
	 * @Throws:
	 */
	public Map<String, String>  querySpliteEnvelopeByUserId(Connection conn,
			Long userId) throws SQLException, DataException {
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT tse.*, ");
		sql.append(" (                                                  ");
		sql.append(" CASE                                               ");
		sql.append(" WHEN to_days(now()) <= to_days(tse.deadTime) THEN  ");
		sql.append("   0                                                ");
		sql.append(" ELSE                                               ");
		sql.append("   1                                                ");
		sql.append(" END) AS isDead                                     ");
		sql.append(" FROM t_splite_envelope tse ");
		sql.append(" WHERE userId = " + userId);
		DataSet dataSet = MySQL.executeQuery(conn, sql.toString());
		return BeanMapUtils.dataSetToMap(dataSet);
	}
	
	
	
	public Map<String, String>  querySETreeInfo(Connection conn,Long senvId,
			String mobilePhone) throws SQLException, DataException {
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT * ");
		sql.append(" FROM t_splite_envelope_tree ");
		sql.append(" WHERE mobilePhone = " + mobilePhone);
		sql.append(" AND senv_id = " + senvId);
		DataSet dataSet = MySQL.executeQuery(conn, sql.toString());
		return BeanMapUtils.dataSetToMap(dataSet);
	}
	
	public Map<String, String>  queryRedpackRecordById(Connection conn,
			long senvId, String unionid) throws SQLException, DataException {
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT * ");
		sql.append(" FROM t_red_pack_records ");
		sql.append(" WHERE senv_id = " + senvId);
		sql.append(" AND unionid = '" + unionid + "'");
		DataSet dataSet = MySQL.executeQuery(conn, sql.toString());
		return BeanMapUtils.dataSetToMap(dataSet);
	}
	
	public Map<String, String>  queryGainRpStatistics(Connection conn,
			String unionid) throws SQLException, DataException {
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT sum(valMoney) as moneySum,count(id) as gainCounts,sum(isMax) as bestCounts ");
		sql.append(" FROM t_red_pack_records ");
		sql.append(" WHERE unionid = '" + unionid + "'");
		DataSet dataSet = MySQL.executeQuery(conn, sql.toString());
		return BeanMapUtils.dataSetToMap(dataSet);
	}
	
	public Map<String, String>  queryShareRpStatistics(Connection conn,
			String unionid) throws SQLException, DataException {
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT sum(                             ");
		sql.append("        CASE                             ");
		sql.append("        WHEN trp.env_type = 1 THEN       ");
		sql.append("          trp.money * trp.env_count      ");
		sql.append("        ELSE                             ");
		sql.append("          trp.money                      ");
		sql.append("        END) as moneySum                 ");
		sql.append("      , sum(trp.env_count) as shareCount ");
		sql.append(" FROM                                    ");
		sql.append("   t_red_pack trp                        ");
		sql.append(" WHERE trp.unionid = '" + unionid + "'");
		DataSet dataSet = MySQL.executeQuery(conn, sql.toString());
		return BeanMapUtils.dataSetToMap(dataSet);
	}
	
	public Map<String, String>  querySETreeRemainById(Connection conn,
			Long senvId) throws SQLException, DataException {
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT (tse.money - sum(valMoney)) AS remainSum ");
		sql.append(" FROM                                            ");
		sql.append("   t_splite_envelope_tree tset                   ");
		sql.append(" LEFT JOIN t_splite_envelope tse                 ");
		sql.append(" ON tset.senv_id = tse.id                        ");
		sql.append(" WHERE senv_id = " + senvId);
		DataSet dataSet = MySQL.executeQuery(conn, sql.toString());
		return BeanMapUtils.dataSetToMap(dataSet);
	}
	
	public Map<String, String>  queryEnvelopeRemainByPhone(Connection conn,
			String mobilePhone) throws SQLException, DataException {
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT 6000 - ifnull(sum(valMoney), 0) as envelopeRemainSum ");
		sql.append(" FROM t_splite_envelope_tree ");
		sql.append(" WHERE mobilePhone = " + mobilePhone);
		sql.append(" AND receivedStatus = 2 ");
		DataSet dataSet = MySQL.executeQuery(conn, sql.toString());
		return BeanMapUtils.dataSetToMap(dataSet);
	}
	
	/**
	 * @MethodName: insertSpliteEnvelope
	 * @Param: GameDao
	 * @Author: wujc
	 * @Date: 
	 * @Return:
	 * @Descb: 增加兑换码信息
	 * @Throws:
	 */
	public long insertVoucherCode(Connection conn, String openid, String paycode,
			double money,int codeBatch) throws SQLException {
		long returnId = -1;
		StringBuffer command = new StringBuffer();
		command.append(" INSERT INTO t_voucher_paycode (paycode, valMoney, codeBatch, createTime, deadTime,openid) ");
		command.append(" VALUES ('" + paycode + "'");
		command.append(" ," + money);
		command.append(" ," + codeBatch);
		command.append(" ,NOW()");
		command.append(" ,date_add(NOW(),interval 30 day)");
		command.append(" ,'" + openid + "')");
		returnId = MySQL.executeNonQuery(conn, command.toString());
		return returnId;
	}
	/**
	 * @MethodName: insertSpliteEnvelope
	 * @Param: openid
	 * @Author: wujc
	 * @Date: 
	 * @Return:
	 * @Descb: 查询兑换码信息
	 * @Throws:
	 */
	public Map<String, String>  queryVoucherCodeByOpenid(Connection conn,
			String openid) throws SQLException, DataException {
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT * ");
		sql.append(" FROM t_voucher_paycode ");
		sql.append(" WHERE openid = '" + openid + "'");
		DataSet dataSet = MySQL.executeQuery(conn, sql.toString());
		return BeanMapUtils.dataSetToMap(dataSet);
	}
	
	/**
	 * @MethodName: insertGofullVoucher
	 * @Param: GameDao
	 * @Author: wujc
	 * @Date: 
	 * @Return:
	 * @Descb: 兑换理财共富券
	 * @Throws:
	 */
	public long insertGofullVoucher(Connection conn, long userId,
			String amount, int usableCounts, int maxUsedTimes,
			String productLimit, int voucherType, String exchangeRate,
			int getType) throws SQLException {
		long returnId = -1;
		StringBuffer command = new StringBuffer();
		command.append(" INSERT INTO t_gofull_voucher (userId, totalMoney, "
				+ "remainMoney, usableCounts, maxUsedTimes, "
				+ "usableStatus, productLimit, voucherType, "
				+ "exchangeRate, getType, createTime, deadTime) ");
		command.append(" VALUES (" + userId);
		command.append(" ," + amount);
		command.append(" ," + amount);
		command.append(" ," + usableCounts);
		command.append(" ," + maxUsedTimes);
		command.append(" ," + 1);
		command.append(" ,'" + productLimit +"'");
		command.append(" ," + voucherType);
		command.append(" ," + exchangeRate);
		command.append(" ," + getType);
		command.append(" ,NOW() ");
		command.append(" ,date_add(now(), INTERVAL 90 DAY))");
		returnId = MySQL.executeNonQuery(conn, command.toString());
		return returnId;
	}
	
	/**
	 * @MethodName: queryRedPackById
	 * @Param: GameDao
	 * @Author: wujc
	 * @Date: 
	 * @Return:
	 * @Descb: 红包
	 * @Throws:
	 */
	public Map<String, String>  queryRedpackByKey(Connection conn,
			String senvKey) throws SQLException, DataException {
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT * ");
		sql.append(" FROM t_red_pack trp ");
		sql.append(" WHERE trp.senv_key = '" + senvKey + "'");
		DataSet dataSet = MySQL.executeQuery(conn, sql.toString());
		return BeanMapUtils.dataSetToMap(dataSet);
	}
	
}
